﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace File.Api.Utils
{
    public static class ExcelToTableUtils
    {
        /// <summary>
        /// 读取Excel文件第一个Sheet保存到DataTable中
        /// </summary>
        /// <param name="fileNamePath"></param>
        /// <param name="startRowIndexs">开始行项索引</param>
        /// <returns></returns>
        public static DataTable ReadExcelTable(string fileName, Stream fs, int startRowIndex)
        {
            try
            {
                Dictionary<int, int> startRowIndexs = new Dictionary<int, int>();
                startRowIndexs[0] = startRowIndex;
                var ds = ReadExcelByOledb(fileName, fs, startRowIndexs);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
            }
            catch
            {
            }

            return null;
        }

        /// <summary>
        /// Oledb方式读取EXCEL
        /// </summary>
        /// <param name="fileNamePath">文件路径</param>
        /// <returns></returns>
        public static DataSet ReadExcelByOledb(string fileName, Stream fs, Dictionary<int, int> startRowIndexs)
        {
            DataSet dataSet = new DataSet();
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx") > 0)
            {
                workbook = new XSSFWorkbook(fs);
            }
            else if (fileName.IndexOf(".xls") > 0)
            {
                workbook = new HSSFWorkbook(fs);
            }

            try
            {
                for (int i = 0; i <= workbook.NumberOfSheets; i++)
                {
                    int startIndex = 0;
                    if (startRowIndexs != null)
                    {
                        startRowIndexs.TryGetValue(i, out startIndex);
                    }

                    var t = DoReadExcelByOledb(workbook, i, startIndex);
                    if (t != null)
                    {
                        dataSet.Tables.Add(t);
                    }
                }
            }
            catch 
            { 
            }

            fs.Close();
            return dataSet;
        }

        /// <summary>
        /// 料站表读取
        /// </summary>
        /// <param name="fileNamePath">文件路径</param>
        /// <returns></returns>
        public static DataSet ReadItemStationExcel(string fileName, Stream fs, Dictionary<int, int> startRowIndexs)
        {
            DataSet dataSet = new DataSet();
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx") > 0)
            {
                workbook = new XSSFWorkbook(fs);
            }
            else if (fileName.IndexOf(".xls") > 0 || fileName.IndexOf(".xlt") > 0)
            {
                workbook = new HSSFWorkbook(fs);
            }

            try
            {
                if (workbook.NumberOfSheets == 2)
                {
                    for (int i = 0; i <= workbook.NumberOfSheets; i++)
                    {
                        int startIndex = 0;
                        if (startRowIndexs != null)
                        {
                            startRowIndexs.TryGetValue(i, out startIndex);
                        }

                        var t = DoReadExcelByOledb(workbook, i, startIndex);
                        if (t != null)
                        {
                            dataSet.Tables.Add(t);
                        }
                    }
                }
                else if (workbook.NumberOfSheets == 1)
                {
                    dataSet = DoReadItemStationExcel(workbook);
                }
            }
            catch
            {
            }

            fs.Close();
            return dataSet;
        }

        /// <summary>
        /// 日读单导入
        /// </summary>
        /// <param name="updateFile"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="jumpRows"></param>
        /// <param name="colNums"></param>
        /// <returns></returns>
        public static DataTable ExcelImportToDataTable(IFormFile updateFile, int startRow, int startCol)
        { 
            if (updateFile != null)
            {
                string fileName = updateFile.FileName;
                var pos = fileName.LastIndexOf(".");
                var extendName = fileName.Substring(pos);
                var inputStream = updateFile.OpenReadStream();
                IWorkbook wb = WorkbookFactory.Create(inputStream);
                ISheet sheet = wb.GetSheetAt(0);
                if (sheet == null)
                {
                    return null;
                }

                DataTable dt = new DataTable();
                var rowCounts = sheet.LastRowNum;
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                IRow headerRow = sheet.GetRow(startRow);
                ///一行最后一个方格的编号 即总的列数
                for (int j = startCol; j < headerRow.LastCellNum; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null && cell.ToString() != string.Empty)
                    {
                        object o = cell;
                        ///读取Excel格式，根据格式读取数据类型
                        switch (cell.CellType)
                        {
                            ///空数据类型处理
                            case CellType.Blank:
                                o = string.Empty;
                                break;
                            ///字符串类型
                            case CellType.String:
                                o = cell.StringCellValue;
                                break;
                            ///数字类型
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    o = cell.DateCellValue;
                                }
                                else
                                {
                                    o = cell.ToString();
                                }

                                break;
                            case CellType.Formula:
                                ///HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
                                ///IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(wb);
                                o = cell.ToString();
                                break;
                            default:
                                o = string.Empty;
                                break;
                        }

                        var colName = o.ToString();
                        if (dt.Columns.Contains(o.ToString()))
                        {
                            colName = colName + j.ToString();
                        }

                        var col = new DataColumn();
                        col.ColumnName = colName;
                        col.DataType = typeof(string);
                        dt.Columns.Add(col);
                    }
                }

                int colCount = headerRow.LastCellNum;
                while (rows.MoveNext())
                {
                    ///IRow row = (XSSFRow)sheet.GetRow(j);
                    IRow row = (IRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    bool addDR = false;
                    if (row.RowNum <= startRow)
                    {
                        ///The firt row is title,no need import
                        continue;
                    }

                    var index = 0;
                    for (int i = startCol; i < colCount; i++)
                    {
                        if (i >= colCount)
                        {
                            index = 0;
                            break;
                        }

                        ICell cell = row.GetCell(i);
                        ///每行第一个cell为空,break
                        if ((cell == null) || ((i == startCol) && (string.IsNullOrEmpty(cell.ToString()) == true)))
                        {
                            break;
                        }

                        if (cell != null)
                        {
                            object o = cell;
                            ///读取Excel格式，根据格式读取数据类型
                            switch (cell.CellType)
                            {
                                ///空数据类型处理
                                case CellType.Blank:
                                    o = string.Empty;
                                    break;
                                ///字符串类型
                                case CellType.String:
                                    o = cell.StringCellValue;
                                    break;
                                ///数字类型
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        o = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        o = cell.ToString();
                                    }

                                    break;
                                case CellType.Formula:
                                    ///HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
                                    ///IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(wb);
                                    o = cell.ToString();
                                    break;
                                default:
                                    o = string.Empty;
                                    break;
                            }

                            dr[index] = Convert.ToString(o);
                            addDR = true;
                        }

                        index++;
                    }

                    if (addDR)
                    {
                        dt.Rows.Add(dr);
                    }
                }

                return dt;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 实际读取EXCELsheet 
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        private static DataTable DoReadExcelByOledb(IWorkbook workbook, int index, int startRowIndex)
        {
            DataTable dt = new DataTable();
            ISheet sheet = null;
            sheet = workbook.GetSheetAt(index);
            int rfirst = startRowIndex > 0 ? startRowIndex - 1 : sheet.FirstRowNum;
            int rlast = sheet.LastRowNum;
            IRow row = sheet.GetRow(rfirst);
            int cfirst = row.FirstCellNum;
            int clast = row.LastCellNum;
            ////获取sheet页中表的字段值
            for (int i = cfirst; i < clast; i++)
            {
                ////StringCellValue
                if (row.GetCell(i) != null)
                {
                    dt.Columns.Add(row.GetCell(i).ToString(), System.Type.GetType("System.String"));
                }
            }

            if (dt.Columns.Count < clast)
            {
                for (int i = dt.Columns.Count + 1; i <= clast; i++)
                {
                    dt.Columns.Add(new DataColumn());
                }
            }

            row = null;
            ////获取sheet页中表的数据值
            for (int i = rfirst + 1; i <= rlast; i++)
            {
                IRow ir = sheet.GetRow(i);
                if (ir == null || ir.Cells.Count == 0)
                {
                    continue;
                }

                DataRow r = dt.NewRow();
                for (int j = cfirst; j < clast; j++)
                {
                    if (ir.GetCell(j) != null)
                    {
                        r[j] = ir.GetCell(j).ToString();
                    }
                }

                dt.Rows.Add(r);
                ir = null;
                r = null;
            }

            sheet = null;
            return dt;
        }

        /// <summary>
        /// 雅马哈料站数据读取
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        private static DataSet DoReadItemStationExcel(IWorkbook workbook)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            ISheet sheet = null;
            sheet = workbook.GetSheetAt(0);
            int rfirst = sheet.FirstRowNum;
            int rlast = sheet.LastRowNum;
            IRow row = sheet.GetRow(rfirst);
            int cfirst = row.FirstCellNum;
            int clast = row.LastCellNum;

            string[] title = new string[] { "料站编码", "名称", "成品", "版面类型", "线体名称" };

            for (int i = 0; i < title.Length; i++)
            {
                dt.Columns.Add(title[i], System.Type.GetType("System.String"));
            }

            DataRow masterRow = dt.NewRow();
            var code = string.Empty;
            int machineStation = 0, itemStation = 0, itemTypeStation = 0, pageStation = 0, lineStation = 0, station = 0, materialStation = 0, position = 0, qtyStation = 0;
            string page = string.Empty, machineNo = string.Empty, materialNo = string.Empty;

            ////获取sheet页中表的字段值
            for (int i = cfirst; i < clast; i++)
            {
                switch (row.GetCell(i).ToString())
                {
                    case "组件ID":
                        itemStation = i;
                        break;
                    case "产品型号":
                        itemTypeStation = i;
                        break;
                    case "机台编号":
                        machineStation = i;
                        break;
                    case "面板类型":
                        pageStation = i;
                        break;
                    case "线体名称":
                        lineStation = i;
                        break;
                    case "安装号码":
                        station = i;
                        break;
                    case "元件名":
                        materialStation = i;
                        break;
                    case "图样名":
                        position = i;
                        break;
                    case "总数":
                        qtyStation = i;
                        break;
                }
            }

            IRow ir = sheet.GetRow(1);

            if (ir.GetCell(itemStation) != null)
            {
                masterRow[2] = ir.GetCell(itemStation).ToString();
                page = ir.GetCell(pageStation).ToString();
                machineNo = ir.GetCell(machineStation).ToString();
                code = masterRow[2] + machineNo;
                masterRow[3] = page;
                masterRow[1] = ir.GetCell(itemTypeStation).ToString();
                masterRow[4] = ir.GetCell(lineStation).ToString();
            }

            dt.Rows.Add(masterRow);
            ds.Tables.Add(dt);

            DataTable detailDt = new DataTable();
            string[] detailTitle = new string[] { "料站编码", "机台", "栈位", "物料", "版面", "用量", "贴装点" };

            for (int i = 0; i < detailTitle.Length; i++)
            {
                detailDt.Columns.Add(detailTitle[i], System.Type.GetType("System.String"));
            }

            row = null;
            DataRow detailRow;

            for (int i = rfirst + 1; i <= rlast; i++)
            {
                ir = sheet.GetRow(i);

                detailRow = detailDt.NewRow();

                ///机台
                if (ir.GetCell(machineStation) != null)
                {
                    machineNo = ir.GetCell(machineStation).ToString();
                    detailRow[1] = machineNo;

                    if (!code.Contains(machineNo))
                    {
                        code += machineNo;
                    }
                }
                else
                {
                    detailRow[1] = machineNo;
                }

                ///栈位
                if (ir.GetCell(station) != null)
                {
                    var stationNo = ir.GetCell(station).ToString();
                    detailRow[2] = stationNo.PadLeft(3, '0');
                }

                ///物料
                if (ir.GetCell(materialStation) != null)
                {
                    materialNo = ir.GetCell(materialStation).ToString();
                    detailRow[3] = materialNo;
                }
                else
                {
                    continue;
                }

                ///版面
                if (ir.GetCell(pageStation) != null)
                {
                    var nextPage = ir.GetCell(pageStation).ToString();

                    if (!string.IsNullOrWhiteSpace(nextPage) && ir.GetCell(pageStation).ToString() != page)
                    {
                        masterRow[3] = "TB";

                        page = ir.GetCell(pageStation).ToString();
                    }

                    detailRow[4] = page;
                }
                else
                {
                    detailRow[4] = page;
                }

                ///用量
                if (ir.GetCell(qtyStation) != null)
                {
                    detailRow[5] = ir.GetCell(qtyStation).ToString();
                }

                ///贴装点
                if (ir.GetCell(position) != null)
                {
                    detailRow[6] = ir.GetCell(position).ToString();
                }

                detailDt.Rows.Add(detailRow);
                ir = null;
                detailRow = null;
            }

            ds.Tables.Add(detailDt);
            code += machineNo + masterRow[3];

            for (int i = 0; i < detailDt.Rows.Count; i++)
            {
                detailDt.Rows[i][0] = code;
            }

            masterRow[0] = code;
            masterRow = null;
            sheet = null;
            return ds;
        }
    }
}
